/**
 * 
 */
package idv.takeshi.software.productline.bookstore.infrastructure.persistence.jpa;

import java.math.BigInteger;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.persistence.EntityExistsException;
import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.PersistenceContext;
import javax.persistence.PersistenceException;
import javax.persistence.Query;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.Validate;
import org.springframework.stereotype.Repository;

import idv.takeshi.software.productline.bookstore.domain.model.useraccount.IndividualAccount;
import idv.takeshi.software.productline.bookstore.domain.model.useraccount.OrganizationAccount;
import idv.takeshi.software.productline.bookstore.domain.model.useraccount.Role;
import idv.takeshi.software.productline.bookstore.domain.model.useraccount.UserAccount;
import idv.takeshi.software.productline.bookstore.domain.model.useraccount.UserAccountDuplicateException;
import idv.takeshi.software.productline.bookstore.domain.model.useraccount.UserAccountRepository;

/**
 * A model repository implementation for UserAccountRepository.
 * @author takeshi
 *
 */
@Repository("userAccountRepository")
public class UserAccountRepositoryImpl implements UserAccountRepository {
	
	private static final String SQL_FIND_EMAIL_ADDRESSES_BY_ROLE_TYPE =
		"SELECT account.EMAIL_ADDRESS " +
		"FROM USER_ACCOUNT account JOIN ROLE aRole ON(account.ID = aRole.USER_ACCOUNT_ID) " +
		"WHERE aRole.DTYPE = :role ";
	
	private static final String SQL_FIND_BY_ACCOUNT_TYPE =
		"	SELECT u.ID \"ID\" " +
		"	FROM {0} u ";
	
	private static final String SQL_FIND_BY_ACCOUNT_NAME =
		"	SELECT u.ID \"ID\" " +
		"	FROM USER_ACCOUNT u " +
		"	WHERE UPPER(u.USER_NAME) LIKE UPPER(:accountName) ";
	
	private static final String SQL_FIND_BY_ROLE =
		"	SELECT u.ID \"ID\" " +
		"	FROM ROLE r JOIN USER_ACCOUNT u ON(r.USER_ACCOUNT_ID = u.ID) " +
		"	WHERE r.DTYPE = :role ";
	
	private static final String SQL_FIND_BY_ACCOUNT_NAME_ACCOUNT_TYPE_AND_ROLE =
		"SELECT DISTINCT ID " +
		"FROM ( " +
		"	{0} " +
		") ";
	
	private static final String SQL_INTERSECT = " INTERSECT ";
	
	private static final String QL_FIND_BY_USER_ACCOUNT_IDS =
		"SELECT u " +
		"FROM UserAccount u " +
		"WHERE u.id IN({0}) ";
	
	private static final Map<String, String> USER_ACCOUNT_TABLE_NAME_MAPPING = new HashMap<String, String>();
	static {
		USER_ACCOUNT_TABLE_NAME_MAPPING.put(IndividualAccount.class.getSimpleName(), "INDIVIDUAL_ACCOUNT");
		USER_ACCOUNT_TABLE_NAME_MAPPING.put(OrganizationAccount.class.getSimpleName(), "ORGANIZATION_ACCOUNT");
	}
	
	
	@PersistenceContext
	private EntityManager entityManager;

	/* (non-Javadoc)
	 * @see idv.takeshi.software.productline.bookstore.domain.model.useraccount.UserAccountRepository#add(idv.takeshi.software.productline.bookstore.domain.model.useraccount.UserAccount)
	 */
	public void add(UserAccount userAccount) throws UserAccountDuplicateException {
		Validate.notNull(userAccount, "userAccount shall always not be null");
		try {
			this.entityManager.persist(userAccount);
		} catch(PersistenceException  e){
			throw new UserAccountDuplicateException("userAccount:" + userAccount.getAccountName() +
			" is duplicate");
		}
	}

	/* (non-Javadoc)
	 * @see idv.takeshi.software.productline.bookstore.domain.model.useraccount.UserAccountRepository#find(java.lang.Long)
	 */
	public UserAccount findById(Long id) {
		Validate.notNull(id, "id shall always not be null");
		return this.entityManager.find(UserAccount.class, id);
	}

	/* (non-Javadoc)
	 * @see idv.takeshi.software.productline.bookstore.domain.model.useraccount.UserAccountRepository#remove(idv.takeshi.software.productline.bookstore.domain.model.useraccount.UserAccount)
	 */
	public void remove(UserAccount userAccount) {
		Validate.notNull(userAccount, "userAccount shall always not be null");
		this.entityManager.remove(userAccount);
	}

	/* (non-Javadoc)
	 * @see idv.takeshi.software.productline.bookstore.domain.model.useraccount.UserAccountRepository#remove(java.util.Collection)
	 */
	public void remove(Collection<Role> roles) {
		Validate.notEmpty(roles, "roles shall always not be null/empty");
		for(Role role :roles){
			this.entityManager.remove(role);
		}
		// TODO Auto-generated method stub
		
	}

	/* (non-Javadoc)
	 * @see idv.takeshi.software.productline.bookstore.domain.model.useraccount.UserAccountRepository#findByActivationKey(java.lang.String)
	 */
	public UserAccount findByActivationKey(String activationKey) {
		Validate.notEmpty(activationKey, "activationKey shall always not be null/empty");
		UserAccount userAccount = null;
		Query query = this.entityManager.createNamedQuery("findByActivationKey");
		query.setParameter("key", activationKey);
		try{
			userAccount = (UserAccount) query.getSingleResult();
		} catch(NoResultException e){
			//DO NOTHING
		}
		return userAccount;
	}

	/* (non-Javadoc)
	 * @see idv.takeshi.software.productline.bookstore.domain.model.useraccount.UserAccountRepository#findByAccuntName(java.lang.String)
	 */
	public UserAccount findByAccountName(String accountName) {
		UserAccount userAccount = null;
		Validate.notEmpty(accountName, "accountName shall always not be null/empty");
		Query query = this.entityManager.createNamedQuery("findByAccountName");
		query.setParameter("accountName", accountName);
		try{
			userAccount = (UserAccount) query.getSingleResult();
		} catch(NoResultException e){
			//DO NOTHING
		}
		return userAccount;
	}

	/* (non-Javadoc)
	 * @see idv.takeshi.software.productline.bookstore.domain.model.useraccount.UserAccountRepository#findEmailAddressesByRole(java.lang.Class)
	 */
	@SuppressWarnings("unchecked")
	public <T extends Role> List<String> findEmailAddressesByRole(
			Class<T> roleClass) {
		Validate.notNull(roleClass, "roleClass shall always not be null");
		Query query = this.entityManager.createNativeQuery(SQL_FIND_EMAIL_ADDRESSES_BY_ROLE_TYPE);
		query.setParameter("role", roleClass.getSimpleName());
		List<String> emailAddresses = query.getResultList();
		return emailAddresses;
	}
	
	
	/* (non-Javadoc)
	 * @see idv.takeshi.software.productline.bookstore.domain.model.useraccount.UserAccountRepository#findByOrderId(java.lang.Long)
	 */
	public UserAccount findByOrderId(Long orderId){
		Validate.notNull(orderId, "orderId shall always not be null");
		UserAccount userAccount = null;
		Query query = this.entityManager.createNamedQuery("findByOrderId");
		query.setParameter("orderId", orderId);
		try {
			userAccount = (UserAccount) query.getSingleResult();
		} catch(NoResultException e){
			//DO NOTHING
		}
		return userAccount;
	}
	
	/* (non-Javadoc)
	 * @see idv.takeshi.software.productline.bookstore.domain.model.useraccount.UserAccountRepository#update(idv.takeshi.software.productline.bookstore.domain.model.useraccount.UserAccount)
	 */
	public void update(UserAccount userAccount){
		Validate.notNull(userAccount, "userAccount shall always not be null");
		Set<Role> roles = null;
		this.entityManager.merge(userAccount);
		
		roles = userAccount.getRoles();
		if(CollectionUtils.isNotEmpty(roles)){
			for(Role role : roles){
				this.entityManager.merge(role);
			}
		}
	}

	/* (non-Javadoc)
	 * @see idv.takeshi.software.productline.bookstore.domain.model.useraccount.UserAccountRepository#findByRoleAccountNameAndAccountType(java.lang.String, java.lang.String, java.lang.String)
	 */
	@SuppressWarnings("unchecked")
	public Set<UserAccount> findByRoleAccountNameAndAccountType(String role,
			String accountName, String accountType) {
		
		Set<UserAccount> userAccounts = null;
		String roleSql = null;
		String accountNameSql = null;
		String accountTypeSql = null;
		String sql = null;
		String ql = null;
		
		if(StringUtils.isNotBlank(role)){
			roleSql = SQL_FIND_BY_ROLE;
			sql = roleSql;
		}
		
		if(StringUtils.isNotBlank(accountName)){
			accountNameSql = SQL_FIND_BY_ACCOUNT_NAME;
			if(StringUtils.isNotBlank(sql)){
				sql += SQL_INTERSECT + accountNameSql;
			} else{
				sql = accountNameSql;
			}
		}
		
		if(StringUtils.isNotBlank(accountType)){
			accountTypeSql = MessageFormat.format(
					SQL_FIND_BY_ACCOUNT_TYPE, USER_ACCOUNT_TABLE_NAME_MAPPING.get(accountType));
			if(StringUtils.isNotBlank(sql)){
				sql += SQL_INTERSECT + accountTypeSql;
			} else{
				sql = accountTypeSql;
			}
		}
		
		if(StringUtils.isNotBlank(sql)){
			List<Long> ids = new ArrayList<Long>();
			List<Number> tmpIds = null;
			sql = MessageFormat.format(SQL_FIND_BY_ACCOUNT_NAME_ACCOUNT_TYPE_AND_ROLE, sql);
			Query query = this.entityManager.createNativeQuery(sql);
			if(StringUtils.isNotBlank(role))
				query.setParameter("role", role);
			if(StringUtils.isNotBlank(accountName))
				query.setParameter("accountName", "%" + accountName + "%");
			tmpIds = query.getResultList();
			
			for(Number id : tmpIds){
				ids.add(id.longValue());
			}
			
			userAccounts = this.findByIds(ids.toArray(new Long[]{}));
		} else{
			userAccounts = this.findAll();
		}
		return userAccounts;
	}
	
	
	/* (non-Javadoc)
	 * @see idv.takeshi.software.productline.bookstore.domain.model.useraccount.UserAccountRepository#findAll()
	 */
	@SuppressWarnings("unchecked")
	public Set<UserAccount> findAll(){
		List<UserAccount> accounts = null;
		
		Query query = this.entityManager.createNamedQuery("findAllUserAccounts");
		accounts = query.getResultList();
		return new LinkedHashSet<UserAccount>(accounts);
	}
	
	/* (non-Javadoc)
	 * @see idv.takeshi.software.productline.bookstore.domain.model.useraccount.UserAccountRepository#findByIds(java.lang.Long[])
	 */
	@SuppressWarnings("unchecked")
	public Set<UserAccount> findByIds(Long[] ids){
		List<UserAccount> accounts = null;
		
		if(ArrayUtils.isNotEmpty(ids)){
			String idsString = null;
			String ql = null;
			for(int a = 0; a < ids.length; a++){
				if(a == 0){
					idsString = "" + ids[a];
				} else{
					idsString += ", " + ids[a];
				}
			}
			ql = MessageFormat.format(QL_FIND_BY_USER_ACCOUNT_IDS, idsString);
			Query query = this.entityManager.createQuery(ql);
			accounts = query.getResultList();
		} else{
			accounts = new ArrayList<UserAccount>();
		}
		//TODO TBD
		return new LinkedHashSet<UserAccount>(accounts);
	}

	/**
	 * @return the entityManager
	 */
	public EntityManager getEntityManager() {
		return entityManager;
	}

}
